package com.houdoudou.bean;

public class DatabaseAndTable {

	public static String sqlCreateDatabase="CREATE DATABASE IF NOT EXISTS houdoudb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;";
	
	public static String sqlCreateTbFinancer=" create table IF NOT EXISTS financer("
	   		+ "telnum char(11) ,"
	   		+ "secret varchar(100) ,"
	   		+ "type int,"
	   		+" primary key (telnum)"
	   		+ ")";
	public static String sqlCreateTbProgrammer=" create table IF NOT EXISTS programmer("
	   		+ "telnum char(11) ,"
	   		+ "secret varchar(100) ,"
	   		+ "type int,"
	   		+" primary key (telnum)"
	   		+ ")";
	public static String sqlCreateTbOwner=" create table IF NOT EXISTS owner("
	   		+ "telnum char(11) ,"
			+ "serviceTelnum char(11),"
			+ "name  varchar(50),"
	   		+ "address varchar(200),"
	   		+ "secret varchar(100) ,"
	   		+ "isAuth int,"
	   		+ "rate DECIMAL(18,6),"
	   		+" primary key (telnum)"
	   		+ ")";
	
	public static String sqlCreateTbMachine=" create table IF NOT EXISTS machine("
	   		+ "id int(10) NOT NULL AUTO_INCREMENT ,"
			+ "secret varchar(100),"
			+ "owner char(11),"
			+ "boxNum int,"
			+ "boxVolume int,"
			+ "name  varchar(50),"
	   		+ "address varchar(200),"
	   		+ "search  varchar(300) ,"
	   		+ "longitude  DECIMAL(18,8),"
	   		+ "latitude  DECIMAL(18,8),"
	   		+ "inuse int,"
			+ "online int,"
			+ "failure int,"
	   		+ "tempCfg char(3),"
	   		+ "timeCfg char(3),"
	   		+ "timeAEM char(4),"
	   		+ "timeAAM char(4),"
	   		+ "timeTLM char(4),"
	   		+ "timeRLM char(4),"
	   		+ "timeAEN char(4),"
	   		+ "timeAAN char(4),"
	   		+ "timeTLN char(4),"
	   		+ "timeRLN char(4),"
	   		+ "timeAEE char(4),"
	   		+ "timeAAE char(4),"
	   		+ "timeTLE char(4),"
	   		+ "timeRLE char(4),"
	   		+" primary key (id)"
	   		+ ")";
	
	public static String sqlCreateTbBox=" create table IF NOT EXISTS box("
	   		+ "machine  int,"						
	   		+ "id  smallint,"
	   		+ "state tinyint,"	   	
	   		+ "owner  char(11),"
	   		+" primary key (machine,id)"
	   		+ ")";
	public static String sqlCreateTbBoxUsage=" create table IF NOT EXISTS boxUsage("
	   		+ "machine  int,"						
	   		+ "box  smallint,"
	   		+ "boxState tinyint," //指箱格状态。
	   		+ "orderID  bigint,"
	   		+ "owner  char(11),"
	        + "dateAA  char(8)," 
	        + "dateTimeAA  char(14)," //下单时间
            + "timeFlag tinyint,"
	   		+" primary key (machine,box,dateAA,timeFlag)"
	   		+ ")";
	
	public static String sqlCreateTbSupplier=" create table IF NOT EXISTS supplier("
	   		+ "telnum char(11) ,"
	   		+ "secret varchar(100) ,"		
			+ "name  varchar(50),"
	   		+ "address varchar(200),"
	   		+ "owner char(11),"
	   		+ "serviceTelnum char(11),"	   		
	   		+ "isAuth int,"  //是否上线
	   		+ "minuteMini int," //提前下单时间
	   		+ "moneyMini DECIMAL(18,2)," //最小起订
	   		+ "rate DECIMAL(18,6),"  // 业主提成比例
	   		+ "boxFeeSu DECIMAL(18,3),"  // 业主向商家收的箱格使用费
	   		+ "boxFeeCu DECIMAL(18,3),"  // 商家向消费者收的箱格使用费
	   		+" primary key (telnum)"  
	   		+ ")";
	
	public static String sqlCreateTbIndex=" create table IF NOT EXISTS sort("
	   		+ "id int(10) NOT NULL AUTO_INCREMENT ,"						
			+ "name  varchar(50),"
	   		+ "display int,"
	   		+ "supplier char(11),"	
	   		+" primary key (id)"
	   		+ ")";
	public static String sqlCreateTbProduct=" create table IF NOT EXISTS product("
	   		+ "id int(10) NOT NULL AUTO_INCREMENT ,"						
			+ "name  varchar(50),"
	   		+ "sort int(10),"
	   		+ "isAuth int(10),"
	   		+ "owner char(11),"	
	   		+ "supplier char(11),"	
	   		+ "detail text,"
	   		+ "dir  varchar(50),"
	   		+ "sale int(10),"
	   		+ "isHot int(10),"
	   		+ "isCold int(10),"
	   		+ "isNormal int(10),"
	   		+ "type int(10),"
	   		+" primary key (id)"
	   		+ ")";
	
	public static String sqlCreateTbBranch=" create table IF NOT EXISTS branch("
	   		+ "id int(10),"						
	   		+ "product int(10),"
	   		+ "name  varchar(50),"
	   		+ "isAuth int(10),"
	   		+ "volume int(10),"
	   		+ "price DECIMAL(18,3)," 
	   		+" primary key (id,product)"
	   		+ ")";
	public static String sqlCreateTbCustomer=" create table IF NOT EXISTS customer("
	   		+ "telnum  char(11),"	
	   		+ "spareTelnum  char(11),"	   		
	   		+ "secret varchar(100) ,"
	   		+ "paySecret varchar(100) ,"
	   		+ "money DECIMAL(18,3)," 
	   		+" primary key (telnum)"
	   		+ ")";
	public static String sqlCreateTbDistribute=" create table IF NOT EXISTS distribute("
	   		+ "supplier  char(11),"						
	   		+ "machine int(10),"
	   		+ "time int(10),"
	   		+ "owner  char(11),"	
	   		+" primary key (supplier,machine,time)"
	   		+ ")";
	
	

	public static String sqlCreateTbOrder=" create table IF NOT EXISTS tb_order("
	   		+ "id BIGINT NOT NULL AUTO_INCREMENT ,"						
	   		+ "state tinyint,"
	   		+ "productNum smallint," 
	   		+ "secret  varchar(100),"
	   		
	   		+ "volume smallint,"
	   		+ "boxVolume  smallint,"  		
	   		+ "boxNum tinyint,"
	   		+ "isBoxUsed tinyint,"
	   		+ "boxesIn  varchar(100),"
	   		
	   		+ "machineID int,"
	   		+ "machineAddress  varchar(100),"
	   		+ "machineName  varchar(100),"
	   		
            + "dateAA  char(8),"
            + "timeFlag tinyint,"
            
            + "customerTelnum  char(11),"
            + "supplierTelnum  char(11),"
            + "serviceTelnum  char(11),"
            + "supplierName  varchar(100),"
            + "ownerTelnum  char(11),"
            
            + "dateTimePL  char(14),"
            + "dateTimePA  char(14),"
            + "dateTimeAE  char(14),"
            + "dateTimeAA  char(14),"
            + "dateTimeAI  char(14),"
            + "courierAITelnum  char(11),"
            + "dateTimeTK  char(14),"
            + "dateTimeTL  char(14),"
            + "dateTimeRK  char(14),"
            + "courierRKTelnum  char(11),"
            + "dateTimeRL  char(14),"
            + "dateTimeAB  char(14),"
            + "dateTimeRF  char(14),"
            + "courierABTelnum  char(11),"
            
            + "dateTimeRDL  char(14),"
            + "isRefund int(10),"
                      
	   		+ "boxFeeCu DECIMAL(18,3)," 
	   		+ "moneyFreight DECIMAL(18,3),"
	   		+ "moneyProduct DECIMAL(18,3),"
	   		+ "moneyCu DECIMAL(18,3),"
	   		+ "boxFeeSu DECIMAL(18,3),"
	   		+ "rateSu DECIMAL(18,3),"
	   		+ "rateOw DECIMAL(18,3),"
	   		+ "moneyCompany DECIMAL(18,3),"
	   		+ "moneyOwner DECIMAL(18,3),"
	   		+ "moneySupplier DECIMAL(18,3),"
	   		+ "isMoneyDealed tinyint,"
	   		+ "isMoneyPaid tinyint,"
	   		+ "note varchar(300),"
	   		+ "printTimes smallint,"
	   		
	   		+" primary key (id)"
	   		+ ")";
	
	public static String sqlCreateTbRefund=" create table IF NOT EXISTS refund("
	   		+ "orderID  bigint(10),"						
	   		+ "type tinyint,"
	   		+ "state tinyint,"
	   		+ "money DECIMAL(18,3),"
	   		+ "result tinyint,"
	   	    + "reason text,"
	   	    + "dateTimeCP  char(14),"	
	   	    + "dateTimeS  char(14),"
	   	    + "dateTimeSL  char(14),"
	   	    + "dateTimeC  char(14),"
	   	    + "dateTimeCL  char(14),"
	   	    + "dateTimeO  char(14),"
	   	    + "dateTimeOL  char(14),"
	   	    + "dateTimeCC  char(14),"
	        + "supplier   char(11),"
            + "owner     char(11),"
	   		+" primary key (orderID)"
	   		+ ")";
	 
	public static String sqlCreateTbOrderProduct=" create table IF NOT EXISTS orderProduct("
			+ "orderID  bigint,"					
	   		+ "productID int,"
	   		+ "productType tinyint,"
	   		+ "productName  varchar(100),"	
	   		+ "branchID smallint,"
	   		+ "branchName  varchar(100),"	
	   		+ "branchPrice DECIMAL(18,3),"
	   		+ "branchVolume smallint,"
	   		+ "branchNum smallint,"
	   		+ "dir  varchar(100),"	
	   		+" primary key (orderID,productID,branchID)"
	   		+ ")";
	
	public static String sqlCreateTbVerifyTelnum=" create table IF NOT EXISTS telnumverify("
	   		+ "telnum  char(11),"						
	   		+ "code  char(4),"
	   		+ "validTime  char(14)"	
	   		+ ")";
	
	public static String sqlCreateTbCourier=" create table IF NOT EXISTS courier("
	   		+ "telnum  char(11),"		
	   		+ "secret varchar(100) ,"
	   		+ "belong  char(11) ,"
	   		+ "type  tinyint,"
	   		+ "name varchar(100) ,"
	   		+" primary key (telnum)"
	   		+ ")";
	

	public static String sqlCreateTbMaintenancer=" create table IF NOT EXISTS maintenancer("
	   		+ "telnum  char(11),"		
	   		+ "secret varchar(100) ,"
	   		+ "belong  char(11) ,"
	   		+ "type  tinyint,"
	   		+ "name varchar(100) ,"
	   		+" primary key (telnum)"
	   		+ ")";
}


